package com.felix.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.felix.bean.Carshare;
import com.felix.bean.Favor;
import com.felix.bean.Relation;
import com.felix.bean.Temp_mess_list;
import com.felix.bean.User;
import com.felix.dao.QueryDao;
import com.felix.util.MakeSQL;
import com.g3net.database.DataBase;
import com.g3net.database.DataBaseFactory;
import com.g3net.database.DbException;
import com.g3net.tool.CTime;
import com.g3net.tool.PageBean;

public class QueryDaoImpl implements QueryDao {

	public User queryUser(String uname, String pwd) {

		String sql = "select * from user where (mail = ? or mobile= ?) and password = ? ";
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		args.put(1, uname);
		args.put(2, uname);
		args.put(3, pwd);
		try {
			DataBase db = DataBaseFactory.getDataBase();
			List<User> users = db.doQueryClass(User.class, sql, args);
			if (users != null && users.size() > 0) {
				return users.get(0);
			}

		} catch (DbException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public User queryUserById(int uid) {

		String sql = "select * from user where id = ? ";
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		args.put(1, uid);

		try {
			DataBase db = DataBaseFactory.getDataBase();
			List<User> users = db.doQueryClass(User.class, sql, args);
			if (users != null && users.size() > 0) {
				return users.get(0);
			}

		} catch (DbException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public Favor queryFavor(int uid, int cid) {

		String sql = "SELECT * FROM favor WHERE uid = ? AND cid = ?";
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		args.put(1, uid);
		args.put(2, cid);
		try {
			DataBase db = DataBaseFactory.getDataBase();
			List<Favor> users = db.doQueryClass(Favor.class, sql, args);
			if (users != null && users.size() > 0) {
				return users.get(0);
			}

		} catch (DbException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<Map<String, Object>> queryHotCity(int size) throws Exception {
		Connection conn = null;
		Statement stmtCount = null;
		ResultSet rs = null;
		DataBase db = null;
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Map<String, Object> map = null;
		db = DataBaseFactory.getDataBase();
		conn = db.getConnection();
		stmtCount = conn.createStatement();
		rs = stmtCount
				.executeQuery("SELECT from_city,COUNT(cid) AS cityCount FROM carshare where pid=0 GROUP BY from_city ORDER BY cityCount DESC LIMIT "
						+ size);
		while (rs.next()) {
			map = new HashMap<String, Object>();
			map.put("cityName", rs.getString("from_city"));
			map.put("cityCount", rs.getInt("cityCount"));
			list.add(map);

		}

		if (rs != null) {
			rs.close();
		}
		if (stmtCount != null) {
			stmtCount.close();
		}
		if (conn != null) {
			conn.close();
		}

		return list;
	}

	public List<Carshare> queryCarShare(Map<String, String[]> param, PageBean pb)
			throws DbException {
		DataBase db = null;
		List<Carshare> shares = null;
		int page = 1;
		int perPage = 10;
		// String sortstr=param.get("sortByAddTime");

		StringBuffer sb = new StringBuffer(
				"select * from carshare where pid=0 ");
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String mid = MakeSQL.getCarShareSQL(param, vars);
		sb.append(mid);
		if (param.get("sortBy") == null) {
			sb.append(" ORDER BY gotime desc ");
		} else if ("addTime".equals(param.get("sortBy")[0])) {
			sb.append(" ORDER BY add_time desc ");
		}

		if (param.get("pn") != null) {
			page = Integer.parseInt(param.get("pn")[0]);
		}
		if (param.get("ps") != null) {
			perPage = Integer.parseInt(param.get("ps")[0]);
		}
		db = DataBaseFactory.getDataBase();
		shares = db.doPageQueryClass(Carshare.class, sb.toString(), vars, page,
				perPage, pb, null);
		return shares;
	}

	public List<String> queryGotime(Map<String, String[]> param)
			throws Exception {
		DataBase db = null;
		int page = 1;
		int perPage = 10;
		if (param.get("pn") != null) {
			page = Integer.parseInt(param.get("pn")[0]);
		}
		if (param.get("ps") != null) {
			perPage = Integer.parseInt(param.get("ps")[0]);
		}
		List<String> gotime = new ArrayList<String>();
		StringBuffer sa = new StringBuffer(
				"SELECT DISTINCT gotime  FROM (SELECT * FROM carshare WHERE pid=0 ");
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String mid = MakeSQL.getCarShareSQL(param, vars);
		sa.append(mid);
		sa.append(" ORDER BY gotime DESC LIMIT " + (page - 1) * perPage + ","
				+ perPage + ") AS t ");
		db = DataBaseFactory.getDataBase();
		ResultSet res = db.doQuery(sa.toString(), vars).getResultSet();
		while (res.next()) {
			gotime.add(res.getString(1));
		}
		db.close();

		// gotime=db.doQueryClass(String.class, sa.toString(), vars);
		return gotime;
	}

	public List<String> queryFavorGotime(Map<String, String[]> param)
			throws Exception {
		DataBase db = null;
		int page = 1;
		int perPage = 10;
		if (param.get("pn") != null) {
			page = Integer.parseInt(param.get("pn")[0]);
		}
		if (param.get("ps") != null) {
			perPage = Integer.parseInt(param.get("ps")[0]);
		}
		List<String> gotime = new ArrayList<String>();
		StringBuffer sa = new StringBuffer(
				"SELECT DISTINCT gotime  FROM (SELECT cs.* FROM carshare AS cs,favor AS f WHERE cs.cid=f.cid AND f.uid=? ");
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		sa.append(" ORDER BY cs.gotime DESC LIMIT " + (page - 1) * perPage + ","
				+ perPage + ") AS t ");
		vars.put(1, param.get("uid")[0]);
		db = DataBaseFactory.getDataBase();
		ResultSet res = db.doQuery(sa.toString(), vars).getResultSet();
		while (res.next()) {
			gotime.add(res.getString(1));
		}
		db.close();

		// gotime=db.doQueryClass(String.class, sa.toString(), vars);
		return gotime;
	}

	public Carshare queryCarshareInfo(int cid) throws DbException {
		String sql = "SELECT u.name as tb_username,u.sex as tb_usersex,cs.* FROM USER AS u,carshare AS cs WHERE u.id=cs.uid AND cs.cid=?";
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		args.put(1, cid);
		DataBase db = DataBaseFactory.getDataBase();
		List<Carshare> shares = db.doQueryClass(Carshare.class, sql, args);
		if (shares == null || shares.size() == 0) {
			return null;
		}
		return shares.get(0);
	}

	public List<User> queryShareJoiner(int cid, int pid) throws DbException {
		String sql = null;
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		if (pid == 0) {
			sql = "SELECT u.* FROM USER AS u,carshare AS cs,relation AS rel WHERE u.id=rel.passengerid AND rel.cid=cs.cid AND cs.cid=? ";
			args.put(1, cid);
		} else {
			sql = "SELECT u.* FROM USER AS u,carshare AS cs,relation AS rel WHERE u.id=rel.passengerid AND rel.cid=cs.cid AND (cs.cid=? OR cs.cid=?)";
			args.put(1, cid);
			args.put(2, pid);
		}
		DataBase db = DataBaseFactory.getDataBase();
		List<User> userList = db.doQueryClass(User.class, sql, args);
		return userList;
	}

	public List<Carshare> querySubCarShare(int pid) throws DbException {
		String sql = "SELECT u.name as tb_username,u.sex as tb_usersex,cs.* FROM USER AS u,carshare AS cs WHERE u.id=cs.uid AND cs.pid=?";
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		args.put(1, pid);
		DataBase db = DataBaseFactory.getDataBase();
		List<Carshare> shares = db.doQueryClass(Carshare.class, sql, args);
		return shares;
	}

	public List<Carshare> queryMyCarShare(Map<String, String[]> param,
			PageBean pb) throws DbException {

		DataBase db = null;
		List<Carshare> shares = null;
		int page = 1;
		int perPage = 10;
		int temp = 1;
		String nowDt = CTime.formatWholeDate();
		Map<Integer, Object> vars = new HashMap<Integer, Object>();

		StringBuffer sb = new StringBuffer("select * from carshare where 0=0 ");
		if (param.get("uid") != null) {
			sb.append("and uid=? ");
		}
		if (param.get("car_sharer") != null) {
			sb.append("and car_sharer=? ");
		}
		if (param.get("fp") != null) {
			sb.append("and from_city=? ");
		}

		if (param.get("fresh") != null && "old".equals(param.get("fresh")[0])) {
			sb
					.append("AND (CASE WHEN trip_type=0 THEN gotime ELSE returntime END ) <= ? ");
		} else {
			sb
					.append("AND (CASE WHEN trip_type=0 THEN gotime ELSE returntime END ) > ? ");
		}
		if (param.get("uid") != null) {
		vars.put(temp, param.get("uid")[0]);
		temp++;
		}
		if (param.get("car_sharer") != null) {
			vars.put(temp, param.get("car_sharer")[0]);
			temp++;
		}
		if (param.get("fp") != null) {
			vars.put(temp, param.get("fp")[0]);
			temp++;
		}
		vars.put(temp, nowDt);

		if (param.get("sortBy") == null) {
			sb.append(" ORDER BY gotime desc ");
		} else if ("addTime".equals(param.get("sortBy")[0])) {
			sb.append(" ORDER BY add_time desc ");
		}

		if (param.get("pn") != null) {
			page = Integer.parseInt(param.get("pn")[0]);
		}
		if (param.get("ps") != null) {
			perPage = Integer.parseInt(param.get("ps")[0]);
		}
		db = DataBaseFactory.getDataBase();
		shares = db.doPageQueryClass(Carshare.class, sb.toString(), vars, page,
				perPage, pb, null);
		return shares;

	}

	public List<Carshare> queryMyPinChe(Map<String, String[]> param, PageBean pb)
			throws DbException {

		DataBase db = null;
		List<Carshare> shares = null;
		int page = 1;
		int perPage = 10;
		int temp = 1;
		String nowDt = CTime.formatWholeDate();
		Map<Integer, Object> vars = new HashMap<Integer, Object>();

		StringBuffer sb = new StringBuffer(
				"SELECT cs.* FROM carshare AS cs,relation AS r WHERE  cs.cid=r.cid AND cs.uid=r.driverid AND 0=0 ");
		sb.append("and r.passengerid=? ");

		if (param.get("fresh") != null && "old".equals(param.get("fresh")[0])) {
			sb
					.append("AND (CASE WHEN trip_type=0 THEN gotime ELSE returntime END ) <= ? ");
		} else {
			sb
					.append("AND (CASE WHEN trip_type=0 THEN gotime ELSE returntime END ) > ? ");
		}

		vars.put(1, param.get("uid")[0]);

		vars.put(2, nowDt);

		if (param.get("sortBy") == null) {
			sb.append(" ORDER BY gotime desc ");
		} else if ("addTime".equals(param.get("sortBy")[0])) {
			sb.append(" ORDER BY add_time desc ");
		}

		if (param.get("pn") != null) {
			page = Integer.parseInt(param.get("pn")[0]);
		}
		if (param.get("ps") != null) {
			perPage = Integer.parseInt(param.get("ps")[0]);
		}
		db = DataBaseFactory.getDataBase();
		shares = db.doPageQueryClass(Carshare.class, sb.toString(), vars, page,
				perPage, pb, null);
		return shares;

	}

	public List<User> queryMyShareJoiner(String cidStr) throws DbException {
		// TODO Auto-generated method stub

		String sql = "SELECT cs.cid as tb_cid,u.* FROM USER AS u,carshare AS cs,relation AS rel WHERE u.id=rel.passengerid AND rel.cid=cs.cid AND cs.cid in("
				+ cidStr + ")";
		DataBase db = DataBaseFactory.getDataBase();
		List<User> userList = db.doQueryClass(User.class, sql, null);
		return userList;
	}

	public List<Carshare> queryFavors(Map<String, String[]> param, PageBean pb)
			throws DbException {
		DataBase db = null;
		List<Carshare> shares = null;
		int page = 1;
		int perPage = 10;
		Map<Integer, Object> vars = new HashMap<Integer, Object>();

		String sql = "SELECT cs.* FROM carshare AS cs,favor AS f WHERE cs.cid=f.cid AND f.uid=? ORDER BY cs.gotime desc ";
		if (param.get("pn") != null) {
			page = Integer.parseInt(param.get("pn")[0]);
		}
		if (param.get("ps") != null) {
			perPage = Integer.parseInt(param.get("ps")[0]);
		}
		vars.put(1, param.get("uid")[0]);
		db = DataBaseFactory.getDataBase();
		shares = db.doPageQueryClass(Carshare.class, sql, vars, page, perPage,
				pb, null);
		return shares;
	}

	public int checkRegist(String phone)throws Exception {
		DataBase db = null;
		ResultSet res = null;
		int a = 0;
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String sql = "SELECT COUNT(id) FROM USER WHERE mobile=?";
		try {
			vars.put(1, phone);
			db = DataBaseFactory.getDataBase();
			res = db.doQuery(sql, vars).getResultSet();
			if (res.next()) {
				a = res.getInt(1);
			}

		} catch (Exception e) {
			if (db != null) {
				db.close();
			}
			e.printStackTrace();
			// throw new Exception();
		}finally{
			res.close();
			db.close();
			
		}
		return a;
	}
	
	public int checkVote(int uid,int oid) throws Exception {
		DataBase db = null;
		ResultSet res = null;
		int a = 0;
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String sql = "SELECT COUNT(vid) FROM vote WHERE uid=? and obj=?";
		try {
			vars.put(1, uid);
			vars.put(2, oid);
			db = DataBaseFactory.getDataBase();
			res = db.doQuery(sql, vars).getResultSet();
			if (res.next()) {
				a = res.getInt(1);
			}

		} catch (Exception e) {
			if (db != null) {
				db.close();
			}
			e.printStackTrace();
			// throw new Exception();
		}finally{
			res.close();
			db.close();
			
		}
		return a;
	}

	public List<Temp_mess_list> queryCartList(int uid) throws DbException {

		String sql = "SELECT * FROM temp_mess_list WHERE pid=? AND uid <>?";
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		args.put(1, uid);
		args.put(2, uid);
		List<Temp_mess_list> lists = new ArrayList<Temp_mess_list>();
		try {
			DataBase db = DataBaseFactory.getDataBase();
			lists = db.doQueryClass(Temp_mess_list.class, sql, args);
		} catch (DbException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lists;
	}

	public int queryLeftSit(int cid) throws DbException {
		DataBase db = null;
		ResultSet res = null;
		int a = 0;
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String sql = "SELECT sitleft FROM carshare WHERE cid = ?";
		try {
			vars.put(1, cid);
			db = DataBaseFactory.getDataBase();
			res = db.doQuery(sql, vars).getResultSet();
			if (res.next()) {
				a = res.getInt(1);
			}
		} catch (Exception e) {
			if (db != null) {
				db.close();
			}
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}
		}

		return a;
	}

	public Carshare queryCarshare(Carshare csh) throws DbException {

		String sql = "select * from carshare where uid = ? and add_time = ? ";
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		args.put(1, csh.getUid());
		args.put(2, csh.getAdd_time());
		try {
			DataBase db = DataBaseFactory.getDataBase();
			List<Carshare> list = db.doQueryClass(Carshare.class, sql, args);
			if (list != null && list.size() > 0) {
				return list.get(0);
			}

		} catch (DbException e) {
			e.printStackTrace();
		}
		return null;
	}

	public int queryUnRead(Temp_mess_list temp) throws DbException {
		DataBase db = null;
		ResultSet res = null;
		int a = 0;
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String sql = "SELECT unread FROM temp_mess_list WHERE uid = ? AND pid = ? AND cid = ?";
		try {
			vars.put(1, temp.getUid());
			vars.put(2, temp.getPid());
			vars.put(3, temp.getCid());
			db = DataBaseFactory.getDataBase();
			res = db.doQuery(sql, vars).getResultSet();
			if (res.next()) {
				a = res.getInt(1);
			}
		} catch (Exception e) {
			if (db != null) {
				db.close();
			}
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}
		}

		return a;
	}

	public Relation queryRelation(int cid, int driver, int passage)
			throws DbException {

		String sql = "SELECT * FROM relation WHERE cid = ? AND driverid = ? AND passengerid = ?";
		Map<Integer, Object> args = new HashMap<Integer, Object>();
		args.put(1, cid);
		args.put(2, driver);
		args.put(3, passage);
		try {
			DataBase db = DataBaseFactory.getDataBase();
			List<Relation> rels = db.doQueryClass(Relation.class, sql, args);
			if (rels != null && rels.size() > 0) {
				return rels.get(0);
			}

		} catch (DbException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<String> queryCityName(String code) {
		DataBase db = null;
		ResultSet res = null;
		List<String> nList = new ArrayList<String>();
		int a = 0;
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String sql = "SELECT cityName FROM city WHERE shortName LIKE ?";
		try {
			vars.put(1, code + "%");

			db = DataBaseFactory.getDataBase();
			res = db.doQuery(sql, vars).getResultSet();
			while (res.next()) {
				nList.add(res.getString(1));
			}
		} catch (Exception e) {
			if (db != null) {
				db.close();
			}
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}
		}

		return nList;
	}

	public String queryVoteScore(int oid) throws Exception {
		DataBase db = null;
		ResultSet res = null;
		double a = 0;
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String sql = "SELECT AVG(score) FROM vote WHERE obj=?";
		try {
			vars.put(1, oid);
			db = DataBaseFactory.getDataBase();
			res = db.doQuery(sql, vars).getResultSet();
			if (res.next()) {
				a = res.getDouble(1);
				DecimalFormat df=new DecimalFormat("#.#");
				return df.format(a);

			}

		} catch (Exception e) {
			if (db != null) {
				db.close();
			}
			e.printStackTrace();
			// throw new Exception();
		}finally{
			res.close();
			db.close();
			
		}
	
		return "0";
	}

	public int queryAllUnRead(int uid) throws Exception {
		DataBase db = null;
		ResultSet res = null;
		int a = 0;
		Map<Integer, Object> vars = new HashMap<Integer, Object>();
		String sql = "SELECT SUM(unread) FROM temp_mess_list WHERE pid=? and uid<>?";
		try {
			vars.put(1, uid);
			vars.put(2, uid);
			db = DataBaseFactory.getDataBase();
			res = db.doQuery(sql, vars).getResultSet();
			if (res.next()) {
				a = res.getInt(1);
			}
		} catch (Exception e) {
			if (db != null) {
				db.close();
			}
			e.printStackTrace();
		} finally {
			if (res != null) {
				res.close();
			}
			if (db != null) {
				db.close();
			}
		}

		return a;
	}

}
